﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ADOX;
using System.Data.OleDb;
/// <summary>
///AdoxHelper 的摘要说明
/// </summary>
public class AdoxHelper
{
    private CatalogClass cat ;
        
    public AdoxHelper()
    {
        cat= new CatalogClass();      
        cat.let_ActiveConnection(dbconn);
    }
    public static string  dbpath = HttpContext.Current.Server.MapPath("~/app_data/db.mdb");
    public static string dbconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbpath;

    #region 修改字段描述  
    /// <summary>
    /// 修改字段描述
    /// </summary>
    /// <param name="tablename">表名称</param>
    /// <param name="colname">列名称</param>
    /// <param name="description">设置字段描述</param>
    public void ColumnEditDescription(string tablename,string colname,string description)
    {
        Table tbl =cat.Tables[tablename];
        Column col = tbl.Columns[colname];     
        col.Properties["Description"].Value = description;
    } 
    #endregion

    #region 修改列
    public void ColumnUpdate(string tablename, string colname, ColType coltype, int colLength, string defaultvalue, string description, bool allowEmpty)
    {
        Table tbl = cat.Tables[tablename];        
        Column col = tbl.Columns[colname];                  
        switch (coltype)
        {
            case ColType.AutoIncrement:
                {
                    col.Properties["AutoIncrement"].Value = true;
                    col.Type = DataTypeEnum.adInteger;                  
                    break;
                }
            case ColType.Int:
                {
                    col.Type = DataTypeEnum.adDouble;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowEmpty;               
                    break;
                }
            case ColType.String:
                {
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowEmpty;
                    if (colLength > 255 || colLength == 0)
                    {
                        col.Type = ADOX.DataTypeEnum.adLongVarWChar;
                    }
                    else
                    {
                        col.Type = ADOX.DataTypeEnum.adVarChar;
                        col.DefinedSize = colLength;                       
                    }
                    break;
                }
            case ColType.Memo:
                {
                    col.Type = ADOX.DataTypeEnum.adLongVarWChar;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowEmpty;                  
                    break;
                }
            case ColType.DateTime:
                {
                    col.Type = DataTypeEnum.adDate;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowEmpty;                 
                    break;
                }
            case ColType.Boolean:
                {
                    col.Type = ADOX.DataTypeEnum.adBoolean;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowEmpty;               
                    break;
                }
        }
    }
    #endregion



    /// <summary>
    /// 添加列
    /// </summary>
    /// <param name="tablename"></param>
    /// <param name="colname"></param>
    /// <param name="coltype"></param>
    /// <param name="colLength"></param>
    /// <param name="defaultvalue"></param>
    /// <param name="description"></param>
    /// <param name="allowEmpty"></param>
    public void ColumnAdd(string tablename, string colname, ColType coltype,int colLength, string defaultvalue, string description,bool allowDBNull)
    {
        Table tbl = cat.Tables[tablename];
        ColumnClass col = new ColumnClass();
        col.ParentCatalog = cat;
        col.Name = colname;
        col.Properties["Description"].Value = description;
        col.Properties["Default"].Value = defaultvalue;

        switch (coltype)
        {
            case ColType.AutoIncrement:
                {
                    col.Properties["AutoIncrement"].Value = true;
                    col.Type = DataTypeEnum.adInteger;
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);
                    tbl.Keys.Append(colname, KeyTypeEnum.adKeyPrimary,col,tablename,colname);
                    break;
                }
            case ColType.Int:
                {
                    col.Type = DataTypeEnum.adDouble;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowDBNull;
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adDouble, 0);
                    break;
                }
            case ColType.String:
                {
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowDBNull;
                    if (colLength > 255 || colLength == 0)
                    {
                        col.Type = ADOX.DataTypeEnum.adLongVarWChar;
                        tbl.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 16);
                    }
                    else
                    {
                        col.DefinedSize = colLength;
                        tbl.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 0);
                    }
                    break;
                }
            case ColType.Memo:
                {
                    col.Type = ADOX.DataTypeEnum.adLongVarWChar;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowDBNull;
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 16);
                    break;
                }
            case ColType.DateTime:
                {
                    col.Type = DataTypeEnum.adDate;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowDBNull;
                    
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adDate, 0);
                    break;
                }
            case ColType.Boolean:
                {
                    col.Type = ADOX.DataTypeEnum.adBoolean;
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = allowDBNull;
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adBoolean, 0);
                    break;
                }
        }
        tbl = null;
    }

    public void ColumnDelete(string tbname, string colname)
    {
        Table tbl=cat.Tables[tbname];
        try
        {
            tbl.Keys.Delete(colname);
        }
        catch { }
        tbl.Columns.Delete(colname);
    }
    
    public static int ExecuteSql(string SQLString)
    {
        using (OleDbConnection connection = new OleDbConnection(dbconn))
        {
            using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.OleDb.OleDbException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
    }

    public enum ColType
    {
        AutoIncrement,
        Int,
        String,
        DateTime,
        Boolean,
        Memo
    }

    #region 析构函数,释放资源   
    ~AdoxHelper()
    {
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat.ActiveConnection);
        (cat.ActiveConnection as ADODB.Connection).Close();
        cat.ActiveConnection = null;
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(cat);      
        cat = null;
        GC.WaitForPendingFinalizers();
        GC.Collect();
    } 
    #endregion
}
